package com.study.utils.excel;

import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.fastjson2.JSON;
import com.google.common.collect.Lists;
import com.study.utils.excel.Demo.ExcelDTO.KzzBaseDTO;
import com.study.utils.excel.Demo.ExcelDTO.KzzBaseResDTO;
import com.study.utils.excel.Demo.ExcelDTO.KzzFslDTO;
import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.Test;
import org.springframework.beans.BeanUtils;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author zhuzi
 * 2024/12/30 12:16
 */
public class Demo {

    public static BigDecimal syl(BigDecimal end, BigDecimal start) {
        if (start == null) {
            return null;
        }
        BigDecimal div = NumberUtil.div(end, start);
        BigDecimal sub = NumberUtil.sub(div, 1);
        return NumberUtil.mul(sub, 100).setScale(3, RoundingMode.HALF_UP);
    }

    public static List<String> hmLis = Lists.newArrayList("09:40", "09:45", "09:50", "09:55", "10:00", "10:05");

    @Test
    public void list() {
        List<KzzFslDTO> kzzFslDTOS = kzzFslList();
        System.out.println(kzzFslDTOS.size());
    }

    public static List<KzzFslDTO> kzzFslList() {
        String path = "/Users/zhuzi/Downloads/sql/KZZ换手统计/2024（1-11）(1)";
        List<KzzFslDTO> collect = Arrays.stream(FileUtil.ls(path))
                //.limit(10)
                .map(ia -> {
                    System.out.println(ia.getPath());
                    List<KzzFslDTO> list = EasyExcel.read(ia).head(KzzFslDTO.class).sheet()
                            //.headRowNumber(1)
                            .doReadSync();
                    return list;
                }).flatMap(List::stream).filter(ib -> {
                    LocalDateTime rq = ib.getRq();
                    String format = LocalDateTimeUtil.format(rq, "HH:mm");
                    return hmLis.contains(format);
                }).collect(Collectors.toList());
        return collect;
    }

    @Test
    public void readExcel() {
        String path = "/Users/zhuzi/Downloads/sql/KZZ换手统计/2024（1-11）(1)/SH.110044.csv";
        List<KzzFslDTO> list = EasyExcel.read(path).head(KzzFslDTO.class).sheet()
                //.headRowNumber(1)
                .doReadSync();

        list.subList(0, 100).forEach(ia -> {
            System.out.println(ia);
        });
        Map<String, BigDecimal> map = list.stream().limit(10)
                .collect(
                        Collectors.toMap(k -> StrUtil.format("{}{}{}", k.getCode(), k.getRq().toLocalDate(), LocalDateTimeUtil.format(k.getRq(), "HH:mm")),
                                v -> v.getSpj(), (k1, k2) -> k1));
        System.out.println(map);


        System.out.println(list.size());
        System.out.println(syl(new BigDecimal("120"), new BigDecimal("100")));

        List<KzzFslDTO> kzzFslDTOS = kzzFslList();
        Map<String, BigDecimal> mas = kzzFslDTOS.stream()
                .collect(
                        Collectors.toMap(k -> StrUtil.format("{}{}{}", k.getCode(), k.getRq().toLocalDate(), LocalDateTimeUtil.format(k.getRq(), "HH:mm")),
                                v -> v.getSpj(), (k1, k2) -> k1));

        System.out.println(mas);

    }

    @Test
    public void readExcelBase() {
        String path = "/Users/zhuzi/Downloads/sql/KZZ换手统计/00B-01-3日换手率前20.xlsx";
        List<KzzFslDTO> kzzFslDTOS = kzzFslList();
        Map<String, BigDecimal> map = kzzFslDTOS.stream()
                .collect(
                        Collectors.toMap(k -> StrUtil.format("{}{}{}", k.getCode(), k.getRq().toLocalDate(), LocalDateTimeUtil.format(k.getRq(), "HH:mm")),
                                v -> v.getSpj(), (k1, k2) -> k1));

        List<KzzBaseDTO> list = EasyExcel.read(path).head(KzzBaseDTO.class).sheet()
                //.headRowNumber(1)
                .doReadSync();
        List<KzzBaseResDTO> collect = list.stream().map(ia -> {
            //"09:40", "09:45", "09:50", "09:55", "10:00", "10:05"
            KzzBaseResDTO resDTO = new KzzBaseResDTO();
            BeanUtils.copyProperties(ia, resDTO);
            String key = StrUtil.format("{}{}{}", ia.getCode(), ia.getRq1(), "09:40");
            BigDecimal orDefault = map.getOrDefault(key, null);
            if (orDefault == null) {
                System.out.println("数据为空" + key);
            }
            resDTO.setJg940(orDefault);
            resDTO.setJg945(map.getOrDefault(StrUtil.format("{}{}{}", ia.getCode(), ia.getRq1(), "09:45"), null));
            resDTO.setJg950(map.getOrDefault(StrUtil.format("{}{}{}", ia.getCode(), ia.getRq1(), "09:50"), null));
            resDTO.setJg955(map.getOrDefault(StrUtil.format("{}{}{}", ia.getCode(), ia.getRq1(), "09:55"), null));
            resDTO.setJg1000(map.getOrDefault(StrUtil.format("{}{}{}", ia.getCode(), ia.getRq1(), "10:00"), null));
            resDTO.setJg1005(map.getOrDefault(StrUtil.format("{}{}{}", ia.getCode(), ia.getRq1(), "10:05"), null));


            resDTO.setZf940(syl(resDTO.getJg940(), resDTO.getSpj()));
            resDTO.setZf945(syl(resDTO.getJg945(), resDTO.getSpj()));

            resDTO.setZf950(syl(resDTO.getJg950(), resDTO.getSpj()));
            resDTO.setZf955(syl(resDTO.getJg955(), resDTO.getSpj()));

            resDTO.setZf1000(syl(resDTO.getJg1000(), resDTO.getSpj()));
            resDTO.setZf1005(syl(resDTO.getJg1005(), resDTO.getSpj()));

            return resDTO;

        }).collect(Collectors.toList());

        System.out.println(JSON.toJSONString(collect));

        EasyExcel.write("/Users/zhuzi/Downloads/sql/KZZ换手统计/结果.xlsx", KzzBaseResDTO.class).sheet("3日平均换手")
                .doWrite(collect);


        System.out.println(list.size());

    }

    public interface ExcelDTO {

        @Data
        class KzzBaseDTO {

            @ExcelProperty(value = "可转债代码")
            private String code;
            @ExcelProperty(value = "可转债名称")
            private String name;

            @ExcelProperty(value = "收盘价")
            private BigDecimal spj;
            @ExcelProperty(value = "日期")
            private LocalDate rq;

            @ExcelProperty(value = "换手率")
            private BigDecimal hsl;
            @ExcelProperty(value = "3日平均换手率")
            private BigDecimal hslavg;


            @ExcelProperty(value = "1日后收盘价")
            private BigDecimal spj1;

            @ExcelProperty(value = "1日后日期")
            private LocalDate rq1;

            @ExcelProperty(value = "3日后收盘价")
            private BigDecimal spj3;

            @ExcelProperty(value = "3日后日期")
            private LocalDate rq3;

            @ExcelProperty(value = "5日后收盘价")
            private BigDecimal spj5;

            @ExcelProperty(value = "5日后日期")
            private LocalDate rq5;

            @ExcelProperty(value = "平均换手率排序")
            private int rnum;

            @ExcelProperty(value = "1日后涨幅")
            private BigDecimal zf1;
            @ExcelProperty(value = "3日后涨幅")
            private BigDecimal zf3;
            @ExcelProperty(value = "5日后涨幅")
            private BigDecimal zf5;
        }

        @ExcelIgnoreUnannotated
        @Data
        class KzzBaseResDTO {

            @ExcelProperty(value = "可转债代码")
            private String code;
            @ExcelProperty(value = "可转债名称")
            private String name;

            @ExcelProperty(value = "收盘价")
            private BigDecimal spj;
            @ExcelProperty(value = "日期")
            private LocalDate rq;

            @ExcelProperty(value = "换手率")
            private BigDecimal hsl;
            @ExcelProperty(value = "3日平均换手率")
            private BigDecimal hslavg;


            @ExcelProperty(value = "1日后收盘价")
            private BigDecimal spj1;

            @ExcelProperty(value = "1日后日期")
            private LocalDate rq1;

            @ExcelProperty(value = "3日后收盘价")
            private BigDecimal spj3;

            @ExcelProperty(value = "3日后日期")
            private LocalDate rq3;

            @ExcelProperty(value = "5日后收盘价")
            private BigDecimal spj5;

            @ExcelProperty(value = "5日后日期")
            private LocalDate rq5;

            @ExcelProperty(value = "平均换手率排序")
            private int rnum;

            @ExcelProperty(value = "1日后涨幅")
            private BigDecimal zf1;
            @ExcelProperty(value = "3日后涨幅")
            private BigDecimal zf3;
            @ExcelProperty(value = "5日后涨幅")
            private BigDecimal zf5;

            //9:40  9:45 9:50 9:55  10:00  10:05
            @ExcelProperty(value = "9:40价格")
            private BigDecimal jg940;
            @ExcelProperty(value = "9:40收益")
            private BigDecimal zf940;

            @ExcelProperty(value = "9:45价格")
            private BigDecimal jg945;
            @ExcelProperty(value = "9:45收益")
            private BigDecimal zf945;


            @ExcelProperty(value = "9:50价格")
            private BigDecimal jg950;
            @ExcelProperty(value = "9:50收益")
            private BigDecimal zf950;

            @ExcelProperty(value = "9:55价格")
            private BigDecimal jg955;
            @ExcelProperty(value = "9:55收益")
            private BigDecimal zf955;

            @ExcelProperty(value = "10:00价格")
            private BigDecimal jg1000;
            @ExcelProperty(value = "10:00收益")

            private BigDecimal zf1000;
            @ExcelProperty(value = "10:05价格")
            private BigDecimal jg1005;
            @ExcelProperty(value = "10:05收益")
            private BigDecimal zf1005;

            private String hm;


        }

        @Setter
        @Getter
        //@Accessors(chain = true)
        @ToString
        class KzzFslDTO {
            @ExcelProperty(value = "日期", converter = LocalDateTimeConverter.class)
            private LocalDateTime rq;

            @ExcelProperty(value = "代码")
            private String code;

            @ExcelProperty(value = "开盘价")
            private BigDecimal kpj;

            @ExcelProperty(value = "最高价")
            private BigDecimal zgj;

            @ExcelProperty(value = "最低价")
            private BigDecimal zdk;

            @ExcelProperty(value = "收盘价")
            private BigDecimal spj;

            @ExcelProperty(value = "成交量")
            private Long cjl;

            @ExcelProperty(value = "成交金额")
            private Long cje;


          /*  public KzzFslDTO setCode(String code) {
                this.code = StringUtils.substring(code, 3);
                return this;
            }*/

            public String getCode() {
                return StringUtils.substring(code, 3);
            }
        }
    }
}
